package repository

import (
	"context"
	"database/sql"
	"time"

	dbmodel "gitee.com/hp-huiw/family-expense-api/internal/db/model"
	jet "github.com/go-jet/jet/v2/mysql"
)

type Journey struct {
	ID          string    `db:"id"`
	StartDate   time.Time `db:"start_date"`
	EndDate     time.Time `db:"end_date"`
	RegionID    string    `db:"region_id"`
	RegionName  string    `db:"region_name"`
	RegionTitle string    `db:"region_title"`
}

type JourneyRepository interface {
	Query(ctx context.Context, accountGroupID string, pageSize int64, pageIndex int64) ([]Journey, error)
	Insert(ctx context.Context, model *dbmodel.Journey) error
	Update(ctx context.Context, model *dbmodel.Journey) error
	Delete(ctx context.Context, accountGroupID string, id string) error
	DeleteWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string) error
}

func NewJourneyRepository(db *sql.DB) JourneyRepository {
	return &journeyRepository{db: db}
}

// Query queries all journeys by account group id.
func (r *journeyRepository) Query(ctx context.Context, accountGroupID string, pageSize int64, pageIndex int64) ([]Journey, error) {
	var query []struct {
		ID          string    `db:"id"`
		StartDate   time.Time `db:"start_date"`
		EndDate     time.Time `db:"end_date"`
		RegionID    string    `db:"region_id"`
		RegionName  string    `db:"region_name"`
		RegionTitle string    `db:"region_title"`
	}

	stmt := jet.
		SELECT(
			tbJoureny.ID.AS("id"),
			tbJoureny.StartDate.AS("start_date"),
			tbJoureny.EndDate.AS("end_date"),
			tbRegion.ID.AS("region_id"),
			tbRegion.Name.AS("region_name"),
			tbRegion.Title.AS("region_title"),
		).
		FROM(
			tbJoureny.
				INNER_JOIN(tbRegion, tbRegion.ID.EQ(tbJoureny.RegionID)),
		).
		WHERE(
			tbJoureny.AccountGroupID.EQ(jet.String(accountGroupID)),
		).
		ORDER_BY(
			tbJoureny.StartDate.DESC(),
		).
		LIMIT(pageSize).
		OFFSET(pageSize * pageIndex)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		return nil, err
	}

	result := make([]Journey, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// Insert inserts new model.
func (r *journeyRepository) Insert(ctx context.Context, model *dbmodel.Journey) error {
	stmt := tbJoureny.
		INSERT().
		MODEL(model)

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

// Update updates exist journey.
func (r *journeyRepository) Update(ctx context.Context, model *dbmodel.Journey) error {
	stmt := tbJoureny.
		UPDATE(tbJoureny.MutableColumns).
		MODEL(model).
		WHERE(
			tbJoureny.ID.EQ(jet.String(model.ID)),
		)

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

// Delete deletes the Journey with provided account group id and id.
func (r *journeyRepository) Delete(ctx context.Context, accountGroupID string, id string) error {
	stmt := tbJoureny.
		DELETE().
		WHERE(
			tbJoureny.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(tbJoureny.ID.EQ(jet.String(id))),
		)

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

// DeleteWithTx deletes all journeys by account group id with transaction.
func (*journeyRepository) DeleteWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string) error {
	stmt := tbJoureny.
		DELETE().
		WHERE(
			tbJoureny.AccountGroupID.EQ(jet.String(accountGroupID)),
		)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

type journeyRepository struct {
	db *sql.DB
}
